﻿

$$.sql = function(sql){
    var 
         local_DB   = $$.cfg.edk_dbFilePath
        ,test_DB	= $$.cfg.edk_dbFilePath_Test
        ,DB_path	= $$.cfg.edk_isDebugging ? test_DB : Server.mappath(local_DB)
        ,connStr    = "DBQ={0};DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};".format(DB_path)
        ,connObj    = arguments.callee['connObj'] || (function($$_sql_fn_Ref){
            connObj = new ActiveXObject("ADODB.CONNECTION");
            connObj.open(connStr);
            $$_sql_fn_Ref['connObj'] = connObj; 
            return connObj;
        })(arguments.callee)
        ,rsObj      = sql ? connObj.execute(sql) : null;
	
    return {
         connObj: connObj
        ,rsObj  : rsObj
        ,sql    : sql
    };
}
with($$.sql){
	prototype.constructor = $$.sql;

    prototype.list = function(sql, tpl, isPage, appendData){
    	var 
    		 sqlMgr = new $$.sql.Parser(sql)
            ,field	= sqlMgr.getFields()
            ,sql	= isPage ? sqlMgr.autoClass(sql) : sql
            ,isPage = isPage ? this.paging(sql, sqlMgr.getSortField(), true) : null
            ,rsObj 	= $$.sql(isPage ? isPage.sql : sql).rsObj
            /*
            if(e.number==-2146825267){ //该错误代码表示为找不到记录
				RecordArray.push({
					Title:'No Record.'
				}); 				
			}
            */
            ,list	= (function(list){
			        var 
			        	 rawArr	= rsObj.getRows().toArray()
			        	,fields = rsObj.Fields
			        	,fLen 	= fields.Count
						,len  	= rawArr.length / fLen
						,data
						,sp;
							
			        	rsObj.Close();
						
					for (var i = 0; i < len; i++) {
						data = {};
						sp = i * fLen;
						for (var j = 0; j < fLen; j++){
							data[fields(j).name] = sqlMgr.sql2jsValue(rawArr[sp + j], fields);
						}
						list.push(data);
					}
					
					return list;
				})(new Array)
    		,tpl 	= tpl || $$.cfg.Tags('//pure_li/ul')
			,data	= { data : list }
			,data	= $$.apply(data, appendData)
			,html	= new $$.Template(tpl, data).html;
			
		return {
			 sql	 : isPage ? isPage.sql  : sql
			,data	 : list
			,html	 : html
			,paged	 : isPage ? isPage.html : ''
			,connObj : rsObj.ActiveConnection
		};    
    }

    
    prototype.list2SQL	= function(list, sqlTpl){}
    prototype.paging = function(sql, id_fieldName, isMarkup, start, limit){
    	// mix
    	$$.Server.controller.pagingComponent.call(this);
        var 
        	 pageSize     = this.pageSize
        	,start 	      = Number(start || Request.QueryString('start')() || 0)
            ,limit 	      = Number(limit || Request.QueryString('limit')() || pageSize)

		/*
		 * 结论1：Select * 语句对速度影响很大。
		     select * from Adab 用时：35940  (共57个字段)
		    select xhjm,xm,xjztdm,bdm,nj,dwdm from Adab  用时 4186
		    select xhjm,xm  from Adab  用时1626
		    select xm from Adab   用时830
		   可以看得出每增加一个字段，时间会增加几乎是一倍。
		    另外，返回的数据的长度也会影响时间：如  select sfzh from Adab 用时1580  几乎与select xhjm,xm from Adab相同。
		因此，返回的数据量是影响速度最关键的因素。网络上的数据传送成了执行SQL最大的性能问题。
		执行一个selec dwdm,count(xhjm) from Adab的语句，用时106 要汇总计算，但传送数据少，因此速度很快
		 */
		var 
			 arr		= sql.match(/SELECT\s+(.*)\s+FROM\s+(\w+)\s?/i)
			,id_Only	= sql.replace(arr[1], arr[2] + '.ID')
			,Rs			= Server.CreateObject ("ADODB.Recordset")
			,Rs			= Rs.Open(id_Only, $$.sql().connObj, 1 ,1) || Rs
			,Rs			= Rs.EOF && Rs.BOF ? (function(){
				Response.write('没有记录'); throw '没有记录';
				})(): Rs
			,IDs		= []
			,totalCount	= Rs.recordcount // 总数 
			,page		= (start / pageSize) + 1
			,page		= page || 1
			,page		= (page - totalCount) > 0 ? totalCount : page
			,j			= totalCount
			,j			= j - (page - 1) * pageSize
			,i			= 0;
	
		Rs.pagesize    = pageSize;
		Rs.absolutepage = page;
		do {
			IDs.push(Rs('id').value);
//			Response.Write("<p>每条记录信息：" + Rs("id")  + "</p>");
			i++;
			Rs.movenext();
		}while (!Rs.Eof && ( i < pageSize));
	    Rs.close();
	    
		var 
		 	 IDs		= (/\s+WHERE\s+/i.test(sql) ?　" AND " : " WHERE ") + "  news.ID = " + IDs.join(' OR news.ID = ')
		 	,hasSort	= /(\s+ORDER\s+BY\s+[\w\.]+)\s?(ASC|DESC)?/i;
		
		if(!hasSort.test(sql)){
			 sql += IDs;
		}else{
			 IDs += " $1 ASC "; // 必须相反，与原来的ORDER BY！
			 sql = sql.replace(hasSort, IDs);  
		}
         	
        count = totalCount;
    
        
        return {
               sql  : sql
            ,count  : totalCount
            , html  : this.renderPagingBar(totalCount)
        };  
    }
    
    
	/**
	 * 生成上一条记录，下一条记录功能
	 * @param {String} tableName 表名。
	 * @param {Number} opt opt有两个值，0或1.
	 * @return {String}
	 */
    prototype.between = function(tableName, opt) {
		var 
			 html 	= '<a href="?ID={0}">{1}</a>'
			,ID 	= Number(Request.QueryString('ID')())
			,sql 	= opt
					? "SELECT ID, title FROM {0} WHERE ID < {1} ORDER BY ID DESC"
					: "SELECT ID, title FROM {0} WHERE ID > {1} ORDER BY ID"
			,sql 	= sql.format(tableName, ID);

		with (this.constructor(sql).rsObj) {
			html = EOF ? (opt ? "下面没有记录。" : "此为第一条记录。") : html.format(
					fields("ID").value, fields("title").value);
			Close();
		}
		return html;
	}
	// 计数器
	prototype.readHits = function(table) {
		var 
			 sql	  = "UPDATE {0} SET ReadHits = ReadHits + 1 WHERE ID = {1}".format(table, Request('ID')())
			,sql	  = this.constructor(sql)
			,sql	  = "SELECT ReadHits FROM {0} WHERE ID = {1}".format(table, Request('ID')())
			,sql 	  = this.constructor(sql).rsObj
			,readHits = sql('readHits').value;
		
		sql.close();
		return readHits;
	}
}
Object.mix($$.sql, $$.Server.controller.pagingComponent);

$$.sql.Parser = function(sql){
    this.sql   = sql;
}
$$.sql.Parser.prototype = {
	 parse: function(sql){
	 	this.sql = sql;
	 }
    ,getFields: function(){
    	if(this.fields)return this.fields;
    	
        var 
        	 reg = /SELECT\s+(TOP\s+\d+\s+)?(.*)\s+FROM.*/i
        	,arr = this.sql.match(reg);
        
        if(arr[2] == '*'){ // 获取所有的字段。
        	this.fields = [];
        	with($$.sql("SELECT TOP 1 * FROM news").rsObj){
	        	for(var i = 0, j = fields.count; i < j; i++){
	        		this.fields.push(Fields(i).name);
	        	}
	        	close();
        	}
        }else{
            this.fields = arr[2].split(/,\s?/);
        }
    	return this.fields;
    }
	/**
	 * 要注意order和where的顺序
	 * @return {String}
	 */
	,autoClass: function(){
		var hasSort	= /(\s+ORDER\s+BY\s+[\w\.]+)\s?(ASC|DESC)?/i;
		
		var classID = Request.QueryString('ClassID')();
		if( classID){
			classID = Number(classID);
			if(this.isINNER_JOIN.test(this.sql)){
				var tableName = this.sql.match(this.isINNER_JOIN);
				var 
					 sql = ' WHERE {0}.ClassID = {1} '.format(tableName[1], classID)
					,sql = sql + '$1';
				if(hasSort.test(this.sql)){
					this.sql = this.sql.replace(hasSort, sql
//					function(){
//						(' WHERE {0}.ClassID = {1} '.format(tableName[1], classID)) + RegExp.$1
//					}
					);
				}
				return this.sql;
			}else{
				return this.sql + (' WHERE ClassID = ' + classID);
			}
		}else{
			return this.sql;
		}
	}
    ,getValueByField : function(){
    	var 
    		 fields = this.fields
    		,row	= {};
    		
    	if(!fields)throw '要求在获取数据之后方可使用该方法。';	
    	
        for(var i = 0, j = fields.length; i < j; i++){
        	row[fields[i]] =  this.rsObj(fields[i]).value
        }
        return row;
    }
    // private
    ,setRecordSetObject: function(recordSetObject){
	    this.rsObj = recordSetObject;
	    return recordSetObject;
    }
    ,getPagingSql: function(){
    
    }
    ,sql2jsValue: function(a){
    	return a;
    }
    ,isINNER_JOIN : /FROM\s+(.*)\s+INNER JOIN/ 
	
    /**
	 * @parma {String}
	 * @return {String}
	 */
    ,getSortField:function(sortField){
        var 
        	 isINNER_JOIN = this.isINNER_JOIN
        	,sql		  = this.sql
        	,sortField	  = sortField || "createDate"
        	,id_fieldName = isINNER_JOIN.test(sql) ? sql.match(isINNER_JOIN)[1] + ('.' + sortField) : sortField
        
        return id_fieldName;
    }
    ,Warning:function(config){
 		var str ='';
 		var dt = new Date();
		str = "提交错误,请不要提交非法数据\\n你的所做所为已经被记录在案，" +
				"并将保留对你诉讼的权利。\\n-------->你的IP:"+this.getUserIP()+"\\n-------->" +
				"操作时间:"
		str += dt.format(Date.patterns.LongDate);
		str +="\\n-------->操作页面:"+Request.ServerVariables("URL");
		out('<script>alert("'+str+'");</script>');
		if(config.jump){
			out('<script>location.href="http://www.mps.gov.cn";</script>');
		}else{
			out(str);
		}
		End();
 	}
};


//	j= $$.sqlUtil.noInject('SELECT * FROM "news" truncate');
//	debugger;
$$.Model = function(){}
;(function(){
$$.Model.prototype = {
	
};
})();